#!/usr/bin/env python
# -*- coding: utf-8 -*-
# LoopLine.py
# 2011/05/26 EkikaraDb.pyより分離

import sqlite3
import EkikaraDb

#SELECT_TRAIN_TIME_LIST_SQL = u"""
#select station_order, station_name, train_arrival, train_departure
#from train_time
#where timetable_id =? and train_number = ? and url = ?
#order by timetable_id, train_number, url, station_order
#"""
SELECT_TRAIN_TIME_LIST_SQL = u"""
select timetable_id,
       train_number,
       url,
       station_order,
       station_name
from train_time
where timetable_id =? and train_number = ? and url = ?
order by station_order
"""

#
SELECT_DOWN_EKI_TRAIN_STOP_SQL = u"""
select e.eki_order, t.station_order
from oudia_eki e left join train_time t on e.eki_name = t.station_name
where t.timetable_id = ? and t.train_number = ? and t.url = ? and  t.staton_order = ?
"""

SELECT_UP_EKI_TRAIN_STOP_SQL = u"""
select e.eki_order, t.station_order
from oudia_eki e left join train_time t on e.eki_name = t.station_name
where t.timetable_id = ? and t.train_number = ? and t.url = ? and t.station_order = ?
"""

SELECT_TRUE_LINE_STOP_SQL = u"""
select e.eki_order, e.eki_name, s.station_order, s.station_name, s.train_arrival, s.train_departure
from (oudia_eki e left join true_train_stop t on  e.eki_order = t.eki_order) inner join train_time s
      on t.timetable_id = s.timetable_id and
         t.train_number = s.train_number and
         t.url = s.url and
         t.station_order = s.station_order
where t.timetable_id = ? and t.train_number = ? and t.url = ?
order by e.eki_order
"""

COUNT_EXIST_EKI_SQL = u"""
select count(eki_order) cnt
from oudia_eki
where eki_name = ?
"""

COUNT_EKI_SQL = u"""
select eki_name, count(eki_name) cnt
from oudia_eki
group by eki_name
order by eki_name
"""


CHECK_OUDIA_EKI_EXPROLE_FORWARD_SQL = u"""
select count(eki_order) cnt
from oudia_eki
where eki_order > ?
"""

CHECK_OUDIA_EKI_EXPROLE_FORWARD_UP_SQL = u"""
select count(eki_order) cnt
from oudia_eki
where eki_order < ?
"""

CHECK_OUDIA_EKI_EXPROLE_BACKWARD_SQL = u"""
select count(eki_order) cnt
from oudia_eki
where eki_order < ?
"""

CHECK_OUDIA_EKI_EXPROLE_BACKWARD_UP_SQL = u"""
select count(eki_order) cnt
from oudia_eki
where eki_order > ?
"""

SELECT_OUDIA_EKI_EXPROLE_FORWARD_SQL = u"""
select eki_order,
       eki_name
from oudia_eki
where eki_order > ? and
      eki_name = ?
order by eki_order asc
"""

SELECT_OUDIA_EKI_EXPROLE_FORWARD_UP_SQL = u"""
select eki_order,
       eki_name
from oudia_eki
where eki_order < ? and
      eki_name = ?
order by eki_order desc
"""

SELECT_OUDIA_EKI_EXPROLE_BACKWARD_SQL = u"""
select eki_order,
       eki_name
from oudia_eki
where eki_order < ? and
      eki_name = ?
order by eki_order desc
"""

SELECT_OUDIA_EKI_EXPROLE_BACKWARD_UP_SQL = u"""
select eki_order,
       eki_name
from oudia_eki
where eki_order > ? and
      eki_name = ?
order by eki_order asc
"""

CHECK_TRAIN_TIME_EXPROLE_FORWARD_SQL = u"""
select count(station_order) cnt
from train_time
where timetable_id = ? and
      train_number = ? and
      url = ? and
      station_order > ?
"""

CHECK_TRAIN_TIME_EXPROLE_BACKWARD_SQL = u"""
select count(station_order) cnt
from train_time
where timetable_id = ? and
      train_number = ? and
      url = ? and
      station_order < ?
"""

SELECT_TRAIN_TIME_EXPROLE_FORWARD_SQL = u"""
select station_order, station_name
from train_time
where timetable_id = ? and
      train_number = ? and
      url = ? and
      station_order > ?
order by station_order asc
"""

SELECT_TRAIN_TIME_EXPROLE_BACKWARD_SQL = u"""
select station_order, station_name
from train_time
where timetable_id = ? and
      train_number = ? and
      url = ? and
      station_order < ?
order by station_order desc
"""


class ExproleList:  # SUPER CLASS
    u"""
    路線駅リストより導出した路線駅探索リスト、列車停車駅駅リストより導出した列車停車駅探索リストを
    を保持するオブジェクトを生成するクラス。
    """
    def __init__(self, ekikaraDb, target_order):
        self.db = ekikaraDb
        self.target_order = target_order
        self.forwardOk = False  # 前方へ探索可能かどうかを示すフラグ。
        self.backwardOk = False  # 後方へ探索可能かどうかを示すフラグ。
        self.lastForwardOrder = target_order   # 最後に探索したid
        self.lastBackwardOrder = target_order  # 最後に探索したid

    def isForwardOk(self):
        return self.forwardOk

    def isBackwardOk(self):
        return self.backwardOk

    def targetOrder(self):
        return self.target_order


class LineStationExprole(ExproleList):
    u"""路線駅探索リスト

    oudaia_ekiテーブルを対象とする。
    """
    def __init__(self, ekikaraDb, target_order, upDown='down'):
        ExproleList.__init__(self, ekikaraDb, target_order)
        if upDown == 'up':
            self.upDown = 'up'
        else:
            self.upDown = 'down'       # よくわからんのは全部downにしとく。
        # forward方向へ探索可能かどうか調べ、self.forwardOKフラグを設定。
        if self.upDown == 'up':
            rows = self.db.conn.execute(CHECK_OUDIA_EKI_EXPROLE_FORWARD_UP_SQL, (self.target_order, ))  # one tupple
        else:
            rows = self.db.conn.execute(CHECK_OUDIA_EKI_EXPROLE_FORWARD_SQL, (self.target_order, ))  # one tupple
        row = rows.fetchone()
        if row['cnt'] != None:
            self.forwardOk = True
        rows.close()
        # backward方向へ探索可能かどうか調べ、self.backwardOkフラグを設定。
        if self.upDown == 'up':
            rows = self.db.conn.execute(CHECK_OUDIA_EKI_EXPROLE_BACKWARD_UP_SQL, (self.target_order, ))
        else:
            rows = self.db.conn.execute(CHECK_OUDIA_EKI_EXPROLE_BACKWARD_SQL, (self.target_order, ))
        row = rows.fetchone()
        if row['cnt'] != None:
            self.backwardOk = True
        rows.close()

    def searchForward(self, name):
        u"""前方へ駅名nameで検索し、見つかればそのeki_orderを返す。
        """
        result = None
        if self.lastForwardOrder != None:
            if self.upDown == 'up':
                rows = self.db.conn.execute(SELECT_OUDIA_EKI_EXPROLE_FORWARD_UP_SQL,
                                            (self.lastForwardOrder, name))  # one tupple
            else:
                rows = self.db.conn.execute(SELECT_OUDIA_EKI_EXPROLE_FORWARD_SQL,
                                            (self.lastForwardOrder, name))  # one tupple
            row = rows.fetchone()
            if row != None:
                self.lastForwardOrder = row['eki_order']
                result = row['eki_name']
            else:
                self.lastForwardOrder = None
            rows.close()
        return result

    def searchBackward(self, name):
        u"""後方(order by desc)方向へnameで探索し、見つかったらeki_orderを返す。
        """
        result = None
        if self.lastBackwardOrder != None:
            if self.upDown == 'up':
                rows = self.db.conn.execute(SELECT_OUDIA_EKI_EXPROLE_BACKWARD_UP_SQL,
                                            (self.lastBackwardOrder, name))    # one t
            else:
                rows = self.db.conn.execute(SELECT_OUDIA_EKI_EXPROLE_BACKWARD_SQL,
                                            (self.lastBackwardOrder, name))    # one t
            row = rows.fetchone()
            if row != None:
                self.lastBackwardOrder = row['eki_order']
                result = row['eki_name']
            else:
                self.lastBackwardOrder = None
        return result


class TrainStationExprole(ExproleList):
    u"""列車停車駅探索リスト

    """
    def __init__(self, ekikaraDb, timetable_id, train_number, url, target_order):
        ExproleList.__init__(self, ekikaraDb, target_order)
        self.timetable_id = timetable_id
        self.train_number = train_number
        self.url = url
        # 'up'以外は全てdownとみなす
        # forward方向へ探索可能か調べforwardOkフラグを設定。
        rows = self.db.conn.execute(CHECK_TRAIN_TIME_EXPROLE_FORWARD_SQL,
                                    (self.timetable_id,
                                     self.train_number,
                                     self.url,
                                     self.target_order))
        row = rows.fetchone()
        if row['cnt'] > 0:
            self.forwardOk = True
        rows.close()

        # backward方向へ探索可能か調べbackwardOkフラグを設定。
        rows = self.db.conn.execute(CHECK_TRAIN_TIME_EXPROLE_BACKWARD_SQL,
                                    (self.timetable_id,
                                     self.train_number,
                                     self.url,
                                     self.target_order))
        row = rows.fetchone()
        if row['cnt'] > 0:
            self.backwardOk = True
        rows.close()
        self.forwardCursor = self.db.conn.execute(SELECT_TRAIN_TIME_EXPROLE_FORWARD_SQL,
                                                  (self.timetable_id,
                                                   self.train_number,
                                                   self.url,
                                                   self.lastForwardOrder))
        self.backwardCursor = self.db.conn.execute(SELECT_TRAIN_TIME_EXPROLE_BACKWARD_SQL,
                                                   (self.timetable_id,
                                                    self.train_number,
                                                    self.url,
                                                    self.lastBackwardOrder))

    def __del__(self):
        try:
            self.forwardCursor.close()
        finally:
            pass
        try:
            self.backwardCursor.close()
        finally:
            pass

    def fetchForward(self):
        u"""前方(order by asc)方向へ一歩進み、その駅名を返す
        """
        result = None
        if self.lastForwardOrder != None:
            row = self.forwardCursor.fetchone()
            if row != None:
                self.lastForwardOrder = row['station_order']
                result = row['station_name']
            else:
                self.lastForwardOrder = None
                self.forwardCursor.close()
        return result

    def fetchBackward(self):
        u"""後方(order by desc)方向へ一歩進み、駅名を返す。
        """
        result = None
        if self.lastBackwardOrder != None:
            row = self.backwardCursor.fetchone()
            if row != None:
                self.lastBackwardOrder = row['station_order']
                result = row['station_name']
            else:
                self.lastBackwardOrder = None
                self.backwardCursor.close()
        return result


class TimetableLoopLine(EkikaraDb.Timetable):
    u"""

    override setToOudiaEki
    override ekiTimeCursor
    """
    def _setCntEki(self):
        sqlCnt = 0
        try:
            for row in self.db.conn.execute(COUNT_EKI_SQL):
                self._cntEki[row['eki_name']] = row['cnt']
        except sqlite3.Error, e:
            self.logger.debug(u"COUNT_EKI_SQL;An error occurred:", e.args[0])
        self.logger.debug(u"sqlCnt=%d" % (sqlCnt))

    def __init__(self, db, target_timetable_id, logger, isCheckOrder):
        EkikaraDb.Timetable.__init__(self, db, target_timetable_id, logger)
        #oudia_ekiの重複数をあらかじめ求めておく。
        #self._cntEkiExecCnt = 0
        self._cntEki = {}
        #while len(self._cntEki) <= 0:  # なぜか失敗するので成功するまで。
        #    try:
        #        for row in self.db.conn.execute(COUNT_EKI_SQL):
        #            self._cntEki[row['eki_name']] = row['cnt']
        #    except sqlite3.Error, e:
        #        self.logger.debug(u"COUNT_EKI_SQL;An error occurred:", e.args[0])
        #    self._cntEkiExecCnt += 1
        #    self.logger.debug(u"_cntEkiExecCnt=%d" % (self._cntEkiExecCnt))
        #
        #if len(self._cntEki) > 0:
        #    self.logger.debug(self.pp.pformat(self._cntEki))
        #else:
        #    self.logger.debug(u"self_cntEki is empty!!:")
        self.isCheckOrder = isCheckOrder

    def getCntEki(self, name):
        u"""

        __init__()内だと失敗するのでここでoudia_ekiから取得
        """
        while len(self._cntEki) <= 0:
            self._setCntEki()
            #self.logger.debug(self.pp.pformat(self._cntEki))
        if name in self._cntEki:
            return self._cntEki[name]
        else:
            return 0

    def trainTimeList(self, train_number, url):
        u"""検索のsrc側となるtrain_timeのリスト
        """
        self.logger.debug(u"target_timetable=%d,trainTimeList(train_number=%s,url=%s)" %
                          (self.target_timetable, train_number, url))
        return self.db.conn.execute(SELECT_TRAIN_TIME_LIST_SQL, (self.target_timetable, train_number, url))

    def countExistEki(self, ekiName):
        u"""指定の駅名の駅がoudia_ekiに幾つあるか返す。
        """
        return self.db.conn.execute(COUNT_EXIST_EKI_SQL, (ekiName, ))    # one element tuple

#    def getRange(self, target, indexes):
#        u""" targt の index と、 同一駅名の index のリストを受け取り、target の index 範囲を示す
#
#        return; { 'min': min-index, 'target': target-index, 'max':max-index }
#        target-index が　None の時は target そのものが見つからなかった時。
#        min-index が None の時は下端まで達した。0の場合は0に同じ名前がある場合
#        max-index が None の時はlimitまで達した。len(indexes) -1 の時はそこに同じ名前があるとき
#        indexesの中身が昇順に並んでいるという前提である。
#        """
#        result = { 'min':None, 'target':None, 'max': None }
#        for i, v in enumerate(indexes):
#            if v == target:
#                result['target'] = target
#                if i > 0:
#                    result['min'] = indexes[i - 1]
#                if i < len(indexes) - 1:
#                    result['max'] = indexes[i + 1]
#                break
#        return result

    def pick(self,
             train_number,
             url,
             trainStop,
             lastOrder=None,
             updown='down'):
        u"""
        前提:
        路線駅リストに省略が無い前提で探索する。列車停車駅リストの方は通過駅含め
        すべての駅が掲載されているため、路線駅リストに省略があると探索に失敗する。
        路線駅リストを省略形にするのは探索終了後に行う。
        oudia_ekiとマッチする駅名が2つ以上の場合のみここに来る。
        パターン:
        ( 1) [A][ ][ ][ ][ ][ ][ ][ ][ ][ ] and [B][ ][ ][ ][ ][ ][ ][ ][ ][ ] 探索:→
        ( 2) [A][ ][ ][ ][ ][ ][ ][ ][ ][ ] and [ ][ ][ ][ ][B][ ][ ][ ][ ][ ] 探索:→
        ( 3) [A][ ][ ][ ][ ][ ][ ][ ][ ][ ] and [ ][ ][ ][ ][ ][ ][ ][ ][ ][B] 探索:不可
        ( 4) [ ][ ][ ][ ][A][ ][ ][ ][ ][ ] and [B][ ][ ][ ][ ][ ][ ][ ][ ][ ] 探索:→
        ( 5) [ ][ ][ ][ ][A][ ][ ][ ][ ][ ] and [ ][ ][ ][ ][B][ ][ ][ ][ ][ ] 探索:←→
        ( 6) [ ][ ][ ][ ][A][ ][ ][ ][ ][ ] and [ ][ ][ ][ ][ ][ ][ ][ ][ ][B] 探索:←
        ( 7) [ ][ ][ ][ ][ ][ ][ ][ ][ ][A] and [B][ ][ ][ ][ ][ ][ ][ ][ ][ ] 探索:不可
        ( 8) [ ][ ][ ][ ][ ][ ][ ][ ][ ][A] and [ ][ ][ ][ ][B][ ][ ][ ][ ][ ] 探索:←
        ( 9) [ ][ ][ ][ ][ ][ ][ ][ ][ ][A] and [ ][ ][ ][ ][ ][ ][ ][ ][ ][B] 探索:←
        (10) [A]                            and 任意                           探索:不可
        (11) 任意                           and [B]                            探索:不可
        """
        attr = self.getAttr()
        # 列車停車駅と組み合わせた時の探索パターン
        srcExprole = TrainStationExprole(self.db, self.target_timetable, train_number, url, trainStop['station_order'])
        if (not srcExprole.isForwardOk()) and (not srcExprole.isBackwardOk()):
            return None  # 探索不可(上記パターン10)
        dstExproleList = []
        for eki in self.existEki(trainStop['station_name'], lastOrder, updown):
            isForward = False
            isBackward = False
            dstExprole = LineStationExprole(self.db, eki['eki_order'], attr['updown'])
            if srcExprole.isForwardOk() and srcExprole.isBackwardOk():  # パターン 4～6
                if dstExprole.isForwardOk() and dstExprole.isBackwardOk():     # パターン5
                    isForward = True
                    isBackward = True
                elif dstExprole.isForwardOk():  # パターン 4
                    isForward = True
                elif dstExprole.isBackwardOk():  # パターン 6
                    isBackward = True
                else:
                    pass        # both False ... see パターン 11
            elif srcExprole.isForwardOk():     # パターン 1～3
                if dstExprole.isForwardOk() and dstExprole.isBackwardOk():     # パターン2
                    isForward = True
                elif dstExprole.isForwardOk():  # パターン1
                    isForward = True
                elif dstExprole.isBackwardOk():
                    pass        # パターン3
                else:
                    pass        # both False ... see パターン 11
            elif srcExprole.isBackwardOk():    # パターン7～9
                if dstExprole.isForwardOk() and dstExprole.isBackwardOk():     # パターン8
                    isBackward = True
                elif dstExprole.isForwardOk():  # パターン7
                    pass
                elif dstExprole.isBackwardOk():
                    isBackward = True   # パターン9
                else:
                    pass        # both False ... see パターン 11
            else:
                pass    # both false
            if isForward or isBackward:
                dstExproleList.append({'exprole': dstExprole,
                                       'pattern': {'forward':  isForward,
                                                   'backward': isBackward, }})
        self.logger.debug(self.pp.pformat(dstExproleList))
        while len(dstExproleList) > 1:
            for dstIndex in range(len(dstExproleList) - 1, -1, -1):    # 逆順。途中で削除が入ることがあるため
                if dstExproleList[dstIndex]['pattern']['forward']:
                    forwardStationName = srcExprole.fetchForward()
                    if dstExproleList[dstIndex]['exprole'].searchForward(forwardStationName) == None:
                        dstExproleList.pop(dstIndex)    # この駅を取り除く(popした値は捨てる
                        if len(dstExproleList) <= 1:
                            break
                        continue
                if dstExproleList[dstIndex]['pattern']['backward']:
                    backwardStationName = srcExprole.fetchBackward()
                    if dstExproleList[dstIndex]['exprole'].searchBackward(backwardStationName) == None:
                        dstExproleList.pop(dstIndex)    # この駅を取り除く(popした値は捨てる
                        if len(dstExproleList) <= 1:
                            break
                        continue
        self.logger.debug(self.pp.pformat(dstExproleList))
        if len(dstExproleList) == 1:
            return dstExproleList[0]['exprole'].targetOrder()    # eki_order を返す
        else:
            return None  # 見つからなかった!!

    #def trueTrainStopList(self, train_number, url):
    #    self.logger.debug(u"target_timetable=%d,trueTrainStopList(train_number=%s, url=%s" %
    #                      (self.target_timetable, train_number, url))
    #    return self.db.conn.execute(SELECT_TRUE_LINE_STOP_SQL, (self.target_timetable, train_number, url))

    def prepareEkiTimeCursor(self,
                             train_number,
                             url,
                             updown):       # 'down' or 'up'
        u"""列車の停車駅のうち、このオブジェクトが持つ路線駅リストにマッチングしたものだけtrue_train_stopにセット
        返すものはマッチングした数。
        """
        matchCount = 0    # マッチした駅数
        self.logger.debug(u"prepareEkiTimeCursor(train_number=%s,url=%s)" %
                          (train_number, url))
        trainStopList = []
        for row in self.trainTimeList(train_number, url):
            trainStopList.append({'timetable_id':  row['timetable_id'],
                                  'train_number':  row['train_number'],
                                  'url':           row['url'],
                                  'station_order': row['station_order'],
                                  'station_name':  row['station_name'], })
        self.logger.debug(u"prepareEkiTimeCursor;len(trainStopList)=%d" % (len(trainStopList),))
        # for trainStop in self.trainTimeList(train_number, url):
        try:
            lastOrder = None    # 直前のeki_order
            for trainStop in trainStopList:
                pair = None
                self.logger.debug(u"trainStop={'train_number':%s,'station_order':%d,'station_name':%s }" %
                                  (trainStop['train_number'],
                                   trainStop['station_order'],
                                   trainStop['station_name'],))
                numEki = self.getCntEki(trainStop['station_name'])
                self.logger.debug(u"prepareEkiTimeCursor;numEki=%d" % (numEki,))
                if numEki == 1:
                    # 高速化(?)のため、というか旧バージョンはこれなので、候補が1つだけのばあいは
                    # 従来どおり単純にマッチングする。
                    # 厳密に言うとたとえユニークであってもそれは路線駅リストがたまたま一部分を切り出した
                    # だけの可能性があるので全部Loop対応チェックをした方が良いのかもしれない。
                    self.logger.debug(u"prepareEkiTimeCursor; pair == 1 then self.isExistEki(trainStop['station_name'])")
                    pair = self.isExistEki(trainStop['station_name'], lastOrder, updown)
                elif numEki > 1:
                    self.logger.debug(u"prepareEkiTimeCursor; pair > 1 then pick()")
                    pair = self.pick(train_number, url, trainStop, lastOrder, updown)
                else:  # 0
                    self.logger.debug(u"prepareEkiTimeCursor; pair is no match!!")
                    continue  # no match

                # set result to true_train_stop table.
                if pair != None:
                    matchCount += 1
                    self.logger.debug(u"pair=%d" % (pair))
                    try:
                        self.setTrueTrainStop(train_number, url, trainStop['station_order'], pair, byCommit=False)
                        lastOrder = pair        # 直前のeki_orderを更新
                    except sqlite3.Error, e:
                        self.logger.debug(u"setTrueTrainStop;An error occurred:", e.args[0])
                else:
                    # unknown train stop!!
                    # 路線時刻表に駅記載が無い場合または、当該列車停車駅が路線範囲外の場合は問題無い。
                    # ということで レポートするにしてもnoticeに留める。
                    self.logger.debug(u"pair=None,trainStop={'train_number':%s,'station_order':%d,'station_name':%s }" %
                                      (trainStop['train_number'],
                                       trainStop['station_order'],
                                       trainStop['station_name'],))
        except sqlite3.Error, e:
            self.logger.debug(u"prepareEkiTimeCusor trainStopList loop;An error occurred:", e.args[0])
        try:
            self.db.conn.commit()  # commit seTrueTrainStop
        except sqlite3.Error, e:
            self.logger.debug(u"commit setTrueTrainStop;An error occurred:", e.args[0])
        return matchCount

    def ekiTimeCursor(self, train_number, url):
        self.logger.debug(u"target_timetable=%d,ekiTimeCursor(train_number=%s, url=%s)" %
                          (self.target_timetable, train_number, url))
        return self.db.conn.execute(SELECT_TRUE_LINE_STOP_SQL, (self.target_timetable, train_number, url))
